package com.css.fxfzxczh.modules.evaluationSummary.repository.impl;

import com.css.fxfzxczh.modules.evaluationSummary.entity.*;
import com.css.fxfzxczh.modules.evaluationSummary.repository.EnterpriseDirectEconomyRepository;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.List;

@Repository
public class EnterpriseDirectEconomyRepositoryImpl implements EnterpriseDirectEconomyRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public boolean saveEnterpriseDirectEconomy(EnterpriseDirectEconomy enterpriseDirectEconomy) {
        String sql = "insert into xczh_enterprise_direct_economy values(:id,:xsemId,:type,:province,:city,:county,:xstmId,:evaluationSubareaName,:countCoefficient,:loss,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:countType) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(enterpriseDirectEconomy);
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    @Override
    public List<EnterpriseDirectEconomy> getEnterpriseDirectEconomyByXsemId(String xsemId) {
        String sql = "select * from xczh_enterprise_direct_economy where del_flag='0' and xsem_id='"+xsemId+"' order by create_time";
        List<EnterpriseDirectEconomy> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EnterpriseDirectEconomy.class));
        return list;
    }

    @Override
    public List<EnterpriseDirectEconomy> getEnterpriseDirectEconomy(String xsemId,String type) {
        String sql = "select * from xczh_enterprise_direct_economy where del_flag='0' and xsem_id='"+xsemId+"'" +
                "and type='"+type+"' order by create_time";
        List<EnterpriseDirectEconomy> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EnterpriseDirectEconomy.class));
        return list;
    }

    @Override
    public EnterpriseDirectEconomy getEnterpriseDirectEconomyData(String xsemId, String province, String city, String county,String type) {
        String sql = "select *,(loss) as loss from xczh_enterprise_direct_economy where xsem_id='"+xsemId+"' and province='"+province+"' " +
                "and city='"+city+"' and county='"+county+"' and type='"+type+"' and del_flag='0'";
        try {
            EnterpriseDirectEconomy enterpriseDirectEconomy = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EnterpriseDirectEconomy.class));
            return enterpriseDirectEconomy;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void delteEnterpriseDirectEconomyByXsemId(String xsemId) {
        String sql = "delete from xczh_enterprise_direct_economy where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public int getCountByType(String xsemId) {
        String sql = "select count(*) from xczh_enterprise_direct_economy where xsem_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class,xsemId);
        return count;
    }

    @Override
    public int getIndoorOutdoorCountByType(String xsemId) {
        String sql = "select count(*) from xczh_indoor_outdoor_property_losses where xsem_id = ? ";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class,xsemId);
        return count;
    }

    @Override
    public void deleteIndoorOutdoorPropertyLossesByXsemId(String xsemId) {
        String sql = "delete from xczh_indoor_outdoor_property_losses where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public boolean saveIndoorOutdoorPropertyLosses(IndoorOutdoorPropertyLosses indoorOutdoorPropertyLosses) {
        String sql = "insert into xczh_indoor_outdoor_property_losses values(:id,:xsemId,:type,:province,:city,:county,:xstmId,:evaluationSubareaName,:indoorCountCoefficient,:outdoorCountCoefficient," +
                ":indoorLoss,:outdoorLoss,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:countType) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(indoorOutdoorPropertyLosses);
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    @Override
    public List<IndoorOutdoorPropertyLosses> getIndoorOutdoorPropertyLossesByXsemId(String xsemId) {
        String sql = "select * from xczh_indoor_outdoor_property_losses where del_flag='0' and xsem_id='"+xsemId+"' order by create_time";
        List<IndoorOutdoorPropertyLosses> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IndoorOutdoorPropertyLosses.class));
        return list;
    }

    @Override
    public IndoorOutdoorPropertyLosses getIndoorOutdoorPropertyLossesByXsemIdAndCounty(String xsemId, String province, String city, String county) {
        String sql = "select (indoor_loss) as indoorLoss,(outdoor_loss) as outdoorLoss  from xczh_indoor_outdoor_property_losses " +
                "where del_flag='0' and xsem_id='"+xsemId+"' and province='"+province+"' and city = '"+city+"' and county='"+county+"' and type='2'";
        try {
            IndoorOutdoorPropertyLosses indoorOutdoorPropertyLosses = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(IndoorOutdoorPropertyLosses.class));
            return indoorOutdoorPropertyLosses;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void deletePersonnelInjuryAssessmentByXsemId(String xsemId) {
        String sql = "delete from xczh_personnel_injury_assessment where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public int getlifelineSystemCountByType(String xsemId) {
        String sql = "select count(*) from xczh_lifeline_system_loss where xsem_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class,xsemId);
        return count;
    }

    @Override
    public void deleteLifelineSystemLossByXsemId(String xsemId) {
        String sql = "delete from xczh_lifeline_system_loss where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public void savePersonnelInjuryAssessment(PersonnelInjuryAssessment personnelInjuryAssessment) {
        String sql = "insert into xczh_personnel_injury_assessment " +
                "values(:id,:xsemId,:province,:city,:county,:township,:village,:seismicIntensity,:death,:missing,:badlyInjured,:minorInjury," +
                ":totalPerson,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:xlrmId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(personnelInjuryAssessment);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<PersonnelInjuryAssessment> getPersonnelInjuryData(String xsemId) {
        String sql = "select * from xczh_personnel_injury_assessment where xsem_id='"+xsemId+"' and del_flag = '0'";
        List<PersonnelInjuryAssessment> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PersonnelInjuryAssessment.class));
        return list;
    }

    @Override
    public boolean saveLifelineSystemLoss(LifelineSystemLoss lifelineSystemLoss) {
        String sql = "insert into xczh_lifeline_system_loss values(:id,:xsemId,:type,:province,:city,:county,:lifelineType,:xstmId,:evaluationSubareaName,:countCoefficient,:loss,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:enterpriseName)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lifelineSystemLoss);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public List<LifelineSystemLoss> getLifelineSystemLossData(String xsemId, String type) {
        String sql = "select * from xczh_lifeline_system_loss where xsem_id='"+xsemId+"' and type = '"+type+"' and del_flag = '0' order by create_time";
        List<LifelineSystemLoss> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LifelineSystemLoss.class));
        return list;
    }

    @Override
    public List<LifelineSystemLoss> getLifelineSystemLossByXsemIdAndCounty(String xsemId, String province, String city, String county,String type) {
        String sql = "select *,(loss ) AS loss from xczh_lifeline_system_loss where xsem_id='"+xsemId+"' and type = '"+type+"' and del_flag = '0' " +
                "and province='"+province+"' and city = '"+city+"' and county='"+county+"'";
        List<LifelineSystemLoss> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LifelineSystemLoss.class));
        return list;
    }

    @Override
    public LifelineSystemLoss getTotalLoss(String xsemId, String type) {
        String sql = "select * from xczh_lifeline_system_loss where xsem_id='"+xsemId+"' and del_flag = '0' and type = '"+type+"' and lifeline_type='灾区'";
        try {
            LifelineSystemLoss lifelineSystemLoss = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(LifelineSystemLoss.class));
            return lifelineSystemLoss;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean saveEarthquakeReliefCostAssessment(EarthquakeReliefCostAssessment earthquakeReliefCostAssessment) {
        String sql = "insert into xczh_earthquake_relief_cost_assessment values(:id,:xsemId,:disasterAreaName,:costs,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(earthquakeReliefCostAssessment);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public void deleteEarthquakeReliefCostAssessment(String xsemId) {
        String sql = "delete from xczh_earthquake_relief_cost_assessment where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public EarthquakeReliefCostAssessment getEarthquakeReliefCostAssessment(String xsemId) {
        try {
            String sql = "select * from xczh_earthquake_relief_cost_assessment where del_flag='0' and xsem_id = '"+xsemId+"'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EarthquakeReliefCostAssessment.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void deleteResettlementPopulationEvaluation(String xsemId) {
        String sql = "delete from xczh_resettlement_population_evaluation where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public boolean saveResettlementPopulationEvaluation(ResettlementPopulationEvaluation resettlementPopulationEvaluation) {
        String sql = "insert into xczh_resettlement_population_evaluation values(:id,:xsemId,:disasterAreaName,:aValue,:bValue,:cValue,:dValue,:eValue,:fValue,:resettlePeopleNumber,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(resettlementPopulationEvaluation);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public ResettlementPopulationEvaluation getResettlementPopulationEvaluation(String xsemId) {
        try {
            String sql = "select * from xczh_resettlement_population_evaluation where del_flag='0' and xsem_id = '"+xsemId+"'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResettlementPopulationEvaluation.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean saveEconomicLossesSummary(EconomicLossesSummary economicLossesSummary) {
        String sql = "insert into xczh_economic_losses_summary values(:id,:xsemId,:eventName,:eventTime,:province,:city,:county,:ruralResidential,:ruralPublic,:urbanResidential,:urbanPublic,:governmentOffice,:educationSystem,:healthcareSystem,:otherUsage,:electricity,:waterSupplyAndDrainage,:gasSupply,:heatingSupply,:traffic,:oilAndGasPipelines,:communication,:broadcastingAndTelevision,:municipalFacilities,:railway,:enterprise,:waterConservancy,:culturalHeritage,:other,:indoorProperty,:outdoorProperty,:total,:delFlag,:createUser,:createTime,:updateUser,:updateTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(economicLossesSummary);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public List<EconomicLossesSummary> getEconomicLossesSummaryData(String xsemId) {
        String sql = "select * from xczh_economic_losses_summary where xsem_id='"+xsemId+"' and del_flag = '0' order by create_time";
        List<EconomicLossesSummary> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EconomicLossesSummary.class));
        return list;
    }

    @Override
    public void deleteEconomicLossesSummary(String xsemId) {
        String sql = "delete from xczh_economic_losses_summary where xsem_id = ? ";
        jdbcTemplate.update(sql,xsemId);
    }

    @Override
    public BigDecimal getEconomicLossesSummaryTotal(String xsemId) {
        String sql = "select sum(total) from xczh_economic_losses_summary where xsem_id='"+xsemId+"' and del_flag='0'";
        BigDecimal total = jdbcTemplate.queryForObject(sql, BigDecimal.class);
        return total;
    }

    @Override
    public void batchSave(List<IndoorOutdoorPropertyLosses> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into xczh_indoor_outdoor_property_losses values(:id,:xsemId,:type,:province,:city,:county,:xstmId,:evaluationSubareaName," +
                ":indoorCountCoefficient,:outdoorCountCoefficient,:indoorLoss,:outdoorLoss,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:countType)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<IndoorOutdoorPropertyLosses> getIndoorOutdoorPropertyLossesByXsemId(String xsemId, String type) {
        String sql = "select * from xczh_indoor_outdoor_property_losses where del_flag='0' and xsem_id='"+xsemId+"' and type = '"+type+"' order by create_time";
        List<IndoorOutdoorPropertyLosses> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(IndoorOutdoorPropertyLosses.class));
        return list;
    }

    @Override
    public void batchSaveLifeline(List<LifelineSystemLoss> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into xczh_lifeline_system_loss values(:id,:xsemId,:type,:province,:city,:county,:lifelineType,:xstmId," +
                ":evaluationSubareaName,:countCoefficient,:loss,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:countType)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void batchSaveEnterprise(List<EnterpriseDirectEconomy> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into xczh_enterprise_direct_economy values(:id,:xsemId,:type,:province,:city,:county,:xstmId,:evaluationSubareaName," +
                ":countCoefficient,:loss,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:countType) ";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public PersonnelInjuryAssessment getPeopleNumberByXsemId(String xsemId) {
        try {
            String sql = "SELECT COALESCE(SUM(death), '0') as death,COALESCE(SUM(badly_injured), '0') as badlyInjured," +
                    "COALESCE(SUM(minor_injury), '0') as minorInjury, COALESCE(SUM(missing), '0') as missing " +
                    "FROM xczh_personnel_injury_assessment WHERE del_flag = '0' AND xsem_id = '"+xsemId+"'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(PersonnelInjuryAssessment.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public EconomicLossesSummary getDirEcoLossesByXsemId(String xsemId) {
        try {
            String sql = "select * from xczh_economic_losses_summary where del_flag='0' and xsem_id='"+xsemId+"'" +
                    "and province='分项合计'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EconomicLossesSummary.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public ResettlementPopulationEvaluation getResettlePeopleByXsemId(String xsemId) {
        try {
            String sql = "select * from xczh_resettlement_population_evaluation where del_flag='0' and xsem_id='"+xsemId+"'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResettlementPopulationEvaluation.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<LifelineSystemLoss> getReportLossByXsemId(String xsemId) {
        String sql = "SELECT lifeline_type,sum(loss) loss FROM xczh_lifeline_system_loss where del_flag='0' and xsem_id='"+xsemId+"'" +
                "and lifeline_type!='灾区' group by lifeline_type";
        List<LifelineSystemLoss> list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LifelineSystemLoss.class));
        return list;
    }

}
